package com.huaxia.dao.goOn;

import com.huaxia.pojo.goOn.SaleDeptGoOn;

import org.apache.ibatis.annotations.Select;


import java.util.List;

/**
 * @author wangli 爱我华夏，爱我中华！
 * @date 2020/06/10 17:14
 */

public interface SaleDeptGoOnMapper {

    @Select("select t1.saleDeptsname,\n" +
            "nvl(t2.saleDept13jMonPaid,0)saleDept13jMonPaid,nvl(t2.saleDept13jMonWritten,0)saleDept13jMonWritten,round(decode(nvl(t2.saleDept13jMonWritten,0),0,-9999,t2.saleDept13jMonPaid*100/t2.saleDept13jMonWritten),1) saleDept13jMonYield,\n" +
            "nvl(t3.saleDept13jYearPaid,0)saleDept13jYearPaid,nvl(t3.saleDept13jYearWritten,0)saleDept13jYearWritten,round(decode(nvl(t3.saleDept13jYearWritten,0),0,-9999,t3.saleDept13jYearPaid*100/t3.saleDept13jYearWritten),1) saleDept13jYearYield,\n" +
            "nvl(t4.saleDept25jMonPaid,0)saleDept25jMonPaid,nvl(t4.saleDept25jMonWritten,0)saleDept25jMonWritten,round(decode(nvl(t4.saleDept25jMonWritten,0),0,-9999,t4.saleDept25jMonPaid*100/t4.saleDept25jMonWritten),1) saleDept25jMonYield,\n" +
            "nvl(t5.saleDept25jYearPaid,0)saleDept25jYearPaid,nvl(t5.saleDept25jYearWritten,0)saleDept25jYearWritten,round(decode(nvl(t5.saleDept25jYearWritten,0),0,-9999,t5.saleDept25jYearPaid*100/t5.saleDept25jYearWritten),1) saleDept25jYearYield\n" +
            "from SD_SALEDEPT t1 left join\n" +
            "(select t.managecom saleDeptcode, sum(t.paid_prem)saleDept13jMonPaid, sum(t.written_prem)saleDept13jMonWritten from FACT_ALL_PSTPREM_YEAR13J t\n" +
            "    where stat_date=to_char(sysdate,'yyyymm') and t.salechnl_flag in('02','05') group by t.managecom\n" +
            ")t2 on t1.saleDeptCode=t2.saleDeptCode left join\n" +
            "(select t.managecom saleDeptcode, sum(t.paid_prem)saleDept13jYearPaid, sum(t.written_prem)saleDept13jYearWritten from FACT_ALL_PSTPREM_YEAR13J_cum t\n" +
            "    where substr(stat_date,0,4)=to_char(sysdate,'yyyy') and stat_date<=to_char(sysdate,'yyyymm')  and t.salechnl_flag in('02','05') group by t.managecom\n" +
            ")t3 on t2.saleDeptCode=t3.saleDeptCode left join\n" +
            "(select t.managecom saleDeptcode, sum(t.paid_prem)saleDept25jMonPaid, sum(t.written_prem)saleDept25jMonWritten from FACT_ALL_PSTPREM_YEAR25J t\n" +
            "    where stat_date=to_char(sysdate,'yyyymm')  and t.salechnl_flag in('02','05') group by t.managecom\n" +
            ")t4 on t3.saleDeptCode=t4.saleDeptCode left join\n" +
            "(select t.managecom saleDeptcode, sum(t.paid_prem)saleDept25jYearPaid, sum(t.written_prem)saleDept25jYearWritten from FACT_ALL_PSTPREM_YEAR25J_cum t\n" +
            "    where substr(stat_date,0,4)=to_char(sysdate,'yyyy') and stat_date<=to_char(sysdate,'yyyymm') and t.salechnl_flag in('02','05') group by t.managecom\n" +
            ")t5 on t4.saleDeptCode=t5.saleDeptCode")
    List<SaleDeptGoOn> getSaleDeptGoOn();


}
